package Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import Model.Appointment;
import Model.Approval;
import Model.Staff;
import Model.StaffAppointment;

//import Database.DBConnection;
public class SpecificStaffAppointmentDB {
	
	public static void createAppointmentSpecific(StaffAppointment sa) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "INSERT INTO staffappointment(UA_LoginID, UA_AppointmentID, UA_EffectiveDate, UA_ApptGrpID, UA_MD5, UA_Year, UA_Class, UA_Diploma, UA_EndDate, UA_YearNo, staffappointment.UA_CreatedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try {
			stmt = con.prepareStatement(select);
			System.out.println(select);
			stmt.setString(1, sa.getLoginID());
			stmt.setString(2, sa.getAppointmentID());
			stmt.setString(3, sa.getEffectiveDate());
			stmt.setString(4, sa.getApptGrpID());
			stmt.setString(5, sa.getMd5());
			stmt.setString(6, sa.getYear());
			stmt.setString(7, sa.getSaclass());
			stmt.setString(8, sa.getDiploma());
			stmt.setString(9, sa.getEndDate());
			stmt.setString(10, sa.getYearNo());
			stmt.setString(11, sa.getCreatedBy());
			
			
			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static ArrayList<StaffAppointment> getAppointmentTable() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		//String select = "SELECT Credential_ID, Staff_Name, Role_Name FROM staff INNER JOIN credential ON credential.Credential_ID = staff.Staff_LoginID INNER JOIN role ON role.Role_ID = credential.Credential_Role ORDER BY Staff_Name";
		String select = "SELECT Staff_Name, UA_Year , UA_YearNo, UA_Diploma , UA_Class from specificstaffappointment INNER JOIN staff ON specificstaffappointment.UA_LoginID = staff.Staff_LoginID where UA_appointmentID = 102 ORDER BY Staff_Name";

		ArrayList<StaffAppointment> staffArr = new ArrayList<StaffAppointment>();
		StaffAppointment staff = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staff = new StaffAppointment();
				staff.setName(rs.getString("Staff_Name"));
				staff.setYear(rs.getString("UA_Year"));
				staff.setYearNo(rs.getString("UA_YearNo"));
				staff.setSaclass(rs.getString("UA_Class"));
				staff.setDiploma(rs.getString("UA_Diploma"));

				staffArr.add(staff);
			}
		} catch (Exception e) {
			e.printStackTrace();
			staff = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffArr;
	}

	public static ArrayList<StaffAppointment> getYearNoTable() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		//String select = "SELECT Credential_ID, Staff_Name, Role_Name FROM staff INNER JOIN credential ON credential.Credential_ID = staff.Staff_LoginID INNER JOIN role ON role.Role_ID = credential.Credential_Role ORDER BY Staff_Name";
		String select = "SELECT Staff_Name, UA_YearNo , UA_Diploma , from specificstaffappointment INNER JOIN staff ON specificstaffappointment.UA_LoginID = staff.Staff_LoginID where UA_appointmentID = 20 ORDER BY Staff_Name";

		ArrayList<StaffAppointment> staffArrNo = new ArrayList<StaffAppointment>();
		StaffAppointment staffNo = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				staffNo = new StaffAppointment();
				staffNo.setName(rs.getString("Staff_Name"));
				staffNo.setYear(rs.getString("UA_YearNo"));
			
				staffNo.setDiploma(rs.getString("UA_Diploma"));

				staffArrNo.add(staffNo);
			}
		} catch (Exception e) {
			e.printStackTrace();
			staffNo = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return staffArrNo;
	}

}
